#!/usr/bin/python3
#coding=utf-8

import os
import sys
import datetime
from log import CLog
from mysqlDB import CMySqlDB

def popenLog(str = ""):
    os.popen(str)
    execLog.log(str)

today = datetime.datetime.now().strftime('%Y%m%d')
nowTime = datetime.datetime.now().strftime('%Y%m%d%H%M%S')

execLog = CLog(logName="updateSymbolPricefromRemoteDBLoadSqlFile_" + today + ".log", isPrint=True)
myConfigDB = CMySqlDB(log=execLog, dbHost="81.68.195.26", dbPort=3306,
                  dbUser="shengli", dbPwd="shengli0", dbName="fut_one_config")
myTradingDB = CMySqlDB(log=execLog, dbHost="81.68.195.26", dbPort=3306,
                  dbUser="shengli", dbPwd="shengli0", dbName="fut_one_trading_data")

## 光大上期REM6 整合版148.41
shfeConfigDB = CMySqlDB(log=execLog, dbHost="101.230.233.242", dbPort=43306,
                  dbUser="shengli", dbPwd="shengli0", dbName="fut_one_config")
shfeTradingDB = CMySqlDB(log=execLog, dbHost="101.230.233.242", dbPort=43306,
                  dbUser="shengli", dbPwd="shengli0", dbName="fut_one_trading_data")

# 光大大商所REM15 整合版88.87
dceConfigDB = CMySqlDB(log=execLog, dbHost="175.170.135.88", dbPort=53306,
                  dbUser="shengli", dbPwd="shengli0", dbName="fut_one_config")
dceTradingDB = CMySqlDB(log=execLog, dbHost="175.170.135.88", dbPort=53306,
                  dbUser="shengli", dbPwd="shengli0", dbName="fut_one_trading_data")

#国君中金REM5 整合版225.184
cffexConfigDB = CMySqlDB(log=execLog, dbHost="210.22.104.158", dbPort=11184,
                  dbUser="shengli", dbPwd="shengli0", dbName="fut_one_config")
cffexTradingDB = CMySqlDB(log=execLog, dbHost="210.22.104.158", dbPort=11184,
                  dbUser="shengli", dbPwd="shengli0", dbName="fut_one_trading_data")

def loadDataToSqlFile(data=[], replaceStr = "", sqlFile = None):
    index = 0
    if (None != data and None != replaceStr and None != sqlFile):
        while (index < len(data)):
            execStr = replaceStr
            dataRow = data[index]
            col = 0
            while (col < (len(dataRow) - 1)):
                value = str(dataRow[col])
                if (value.isdigit()):
                    execStr = execStr + value + ","
                else:
                    execStr = execStr + "'" + value + "'" + ","
                col = col + 1

            execStr = execStr + "'" + str(dataRow[col]) + "'" + ");\n"
            sqlFile.write(execStr)
            index = index + 1
    else:
        execLog.log("data is empty when write sql file" + replaceStr)
        return 0

    return index


popenLog("rm -rf  *.sql")
futCategorySqlFile = open("t_symbol_category_"+nowTime+".sql", mode = "w")
futSymbolSqlFile = open("t_china_future_instrument_"+nowTime+".sql", mode = "w")
optSymbolSqlFile = open("t_future_option_instrument_"+nowTime+".sql", mode = "w")
futCloseReportSqlFile = open("t_close_report_"+nowTime+".sql", mode = "w")
optCloseReportSqlFile = open("t_close_report_opt_"+nowTime+".sql", mode = "w")
futCloseIndexSqlFile = open("t_close_report_index_"+nowTime+".sql", mode = "w")
dceComboPrioritySqlFile = open("t_new_dce_combo_priority_"+nowTime+".sql", mode = "w")


#期货品种
replaceStr = "REPLACE INTO t_symbol_category \
                (category_id, exchange_id, exchange_name, category_name, group_id, \
                is_cash_deliv, default_allow_trade, last_update_time) VALUES ("

shfeConfigDB.query(sqlStr="select * from t_symbol_category")
shfeData = shfeConfigDB.getData()
index = loadDataToSqlFile(data=shfeData, replaceStr = replaceStr, sqlFile=futCategorySqlFile)
execLog.log("write shfe fut category finish row count " + str(index))

cffexConfigDB.query(sqlStr="select * from t_symbol_category")
cffexData = cffexConfigDB.getData()
index = loadDataToSqlFile(data=cffexData, replaceStr = replaceStr, sqlFile=futCategorySqlFile)
execLog.log("write cffex fut category finish row count " + str(index))

dceConfigDB.query(sqlStr="select * from t_symbol_category")
dceData = dceConfigDB.getData()
index = loadDataToSqlFile(data=dceData, replaceStr = replaceStr, sqlFile=futCategorySqlFile)
execLog.log("write dce fut category finish row count " + str(index))


#期货合约
replaceStr = "replace into t_china_future_instrument \
                 (symbol, hedge_flag, exchange_id, symbol_desc, exchange_symbol_id, \
                 product_id, product_class, delivery_year, delivery_month, max_market_order_volume, \
                 min_market_order_volume, max_limit_order_volume, min_limit_order_volume, volume_multiple, price_tick, \
                 create_date, open_date, expire_date, start_deliv_date, end_deliv_date, \
                 inst_life_phase, is_trading, position_type, position_date_type, long_margin_ratio, \
                 short_margin_ratio, last_update_date, is_expired) VALUES ("

shfeConfigDB.query(sqlStr="select * from t_china_future_instrument where is_expired = 0; ")
shfeData = shfeConfigDB.getData()
index = loadDataToSqlFile(data=shfeData, replaceStr = replaceStr, sqlFile=futSymbolSqlFile)
execLog.log("write shfe fut symbol finish row count " + str(index))

cffexConfigDB.query(sqlStr="select * from t_china_future_instrument where is_expired = 0; ")
cffexData = cffexConfigDB.getData()
index = loadDataToSqlFile(data=cffexData, replaceStr=replaceStr, sqlFile=futSymbolSqlFile)
execLog.log("write cffex fut symbol finish row count " + str(index))

dceConfigDB.query(sqlStr="select * from t_china_future_instrument where is_expired = 0; ")
dceData = dceConfigDB.getData()
index = loadDataToSqlFile(data=dceData, replaceStr=replaceStr, sqlFile=futSymbolSqlFile)
execLog.log("write dce fut symbol finish row count " + str(index))

#上期期权合约
replaceStr = "REPLACE INTO t_future_option_instrument \
             (symbol, exchange_id, underlying_symbol, call_put, strike_price, \
             expire_date, volume_multiple, price_tick, prod_id, max_hand, \
             min_hand, start_trade_date, end_trade_date, is_expired, last_update_time) VALUES ("

shfeConfigDB.query(sqlStr="select * from t_future_option_instrument where is_expired = 0; ")
shfeData = shfeConfigDB.getData()
index = loadDataToSqlFile(data=shfeData, replaceStr = replaceStr, sqlFile=optSymbolSqlFile)
execLog.log("write shfe opt symbol finish row count " + str(index))

cffexConfigDB.query(sqlStr="select * from t_future_option_instrument where is_expired = 0; ")
cffexData = cffexConfigDB.getData()
index = loadDataToSqlFile(data=cffexData, replaceStr = replaceStr, sqlFile=optSymbolSqlFile)
execLog.log("write cffex opt symbol finish row count " + str(index))

dceConfigDB.query(sqlStr="select * from t_future_option_instrument where is_expired = 0; ")
dceData = dceConfigDB.getData()
index = loadDataToSqlFile(data=dceData, replaceStr = replaceStr, sqlFile=optSymbolSqlFile)
execLog.log("write dce opt symbol finish row count " + str(index))


#上期期货结算价
replaceStr = "REPLACE INTO t_close_report   \
             (symbol, sec_type, exchange, quote_date, symbol_name, \
             high, low, volume, open_price, close_price, \
             settle_price, average_price, bid, ask, upper_limit, \
             lower_limit, update_style, last_updatetime) VALUES ("
# 取最新日期
quoteDate=""
shfeTradingDB.query(sqlStr="select MAX(quote_date) FROM  t_close_report; ")
data = shfeTradingDB.getData()
if(None != data):
    quoteDate = data[0][0]
shfeTradingDB.query(sqlStr="select * from t_close_report where quote_date = " + "'" + quoteDate + "'")
shfeData = shfeTradingDB.getData()
index = loadDataToSqlFile(data=shfeData, replaceStr=replaceStr, sqlFile=futCloseReportSqlFile)
execLog.log("write shfe fut close report finish row count " + str(index))

cffexTradingDB.query(sqlStr="select MAX(quote_date) FROM  t_close_report; ")
data = cffexTradingDB.getData()
if(None != data):
    quoteDate = data[0][0]
cffexTradingDB.query(sqlStr="select * from t_close_report where quote_date = " + "'" + quoteDate + "'")
cffexData = cffexTradingDB.getData()
index = loadDataToSqlFile(data=cffexData, replaceStr=replaceStr, sqlFile=futCloseReportSqlFile)
execLog.log("write cffex fut close report finish row count " + str(index))

dceTradingDB.query(sqlStr="select MAX(quote_date) FROM  t_close_report; ")
data = dceTradingDB.getData()
if(None != data):
    quoteDate = data[0][0]
dceTradingDB.query(sqlStr="select * from t_close_report where quote_date = " + "'" + quoteDate + "'")
dceData = dceTradingDB.getData()
index = loadDataToSqlFile(data=dceData, replaceStr=replaceStr, sqlFile=futCloseReportSqlFile)
execLog.log("write dce fut close report finish row count " + str(index))


#上期期权结算价
replaceStr = "REPLACE INTO t_close_report_opt \
             (symbol, sec_type, exchange, quote_date, underlying_symbol, \
             call_put, strike_price, expire_date, volume, open_price, \
             close_price, settle_price, bid, ask, upper_limit, \
             lower_limit, update_style, last_updatetime) VALUES ("

shfeTradingDB.query(sqlStr="select MAX(quote_date) FROM  t_close_report_opt; ")
data=shfeTradingDB.getData()
if(None != data):
    quoteDate = data[0][0]
shfeTradingDB.query(sqlStr="select * from t_close_report_opt where quote_date = " + "'" + quoteDate + "'")
shfeData = shfeTradingDB.getData()
index = loadDataToSqlFile(data=shfeData, replaceStr=replaceStr, sqlFile=optCloseReportSqlFile)
execLog.log("write shfe opt close report finish row count " + str(index))

cffexTradingDB.query(sqlStr="select MAX(quote_date) FROM  t_close_report_opt; ")
data=cffexTradingDB.getData()
if(None != data):
    quoteDate = data[0][0]
cffexTradingDB.query(sqlStr="select * from t_close_report_opt where quote_date = " + "'" + quoteDate + "'")
cffexData = cffexTradingDB.getData()
index = loadDataToSqlFile(data=cffexData, replaceStr=replaceStr, sqlFile=optCloseReportSqlFile)
execLog.log("write cffex opt close report finish row count " + str(index))

dceTradingDB.query(sqlStr="select MAX(quote_date) FROM  t_close_report_opt; ")
data=dceTradingDB.getData()
if(None != data):
    quoteDate = data[0][0]
dceTradingDB.query(sqlStr="select * from t_close_report_opt where quote_date = " + "'" + quoteDate + "'")
dceData = dceTradingDB.getData()
index = loadDataToSqlFile(data=dceData, replaceStr=replaceStr, sqlFile=optCloseReportSqlFile)
execLog.log("write dce opt close report finish row count " + str(index))



# 中金期权收盘价
replaceStr = "REPLACE INTO t_close_report_index (symbol, exchange, quote_date, close_price, last_update_time) VALUES("


cffexTradingDB.query(sqlStr="select MAX(quote_date) FROM  t_close_report_index; ")
data = cffexTradingDB.getData()
if(0 != len(data)):
    quoteDate = data[0][0]

cffexTradingDB.query(sqlStr="select * from t_close_report_index  where quote_date = " + "'" + quoteDate + "'")
data = cffexTradingDB.getData()
index = loadDataToSqlFile(data=data, replaceStr=replaceStr, sqlFile=futCloseIndexSqlFile)
execLog.log("write cffex index close report finish row count " + str(index))



#大连组合保证金参数
replaceStr = "REPLACE INTO t_new_dce_combo_priority \
            (trading_date, first_symbol, second_symbol, priority, first_settled_price,  \
             second_settled_price, settled_margin_perhand, combo_type, x_ratio) VALUES ("


dceTradingDB.query(sqlStr="select MAX(trading_date) FROM  t_new_dce_combo_priority; ")
data=dceTradingDB.getData()
if(None != data):
    quoteDate = data[0][0]
dceTradingDB.query(sqlStr="select * from t_new_dce_combo_priority where trading_date = "  + "'" + quoteDate + "'")
data = dceTradingDB.getData()
index = loadDataToSqlFile(data=data, replaceStr=replaceStr, sqlFile=dceComboPrioritySqlFile)
execLog.log("write dce combo priority  finish row count " + str(index))


futCategorySqlFile.close()
futSymbolSqlFile.close()
optSymbolSqlFile.close()
futCloseReportSqlFile.close()
optCloseReportSqlFile.close()
futCloseIndexSqlFile.close()
dceComboPrioritySqlFile.close()
